﻿using System;
using System.Threading.Tasks;

using System.Data.OleDb;
using System.Collections.ObjectModel;
using System.IO;
using TestStarter.Model;

namespace TestStarter.Util
{
    public class ExcelDataAccess
    {
        OleDbConnection Conn;
        OleDbCommand Cmd;

        public ExcelDataAccess(string filename)
        {
            string TestFilePath = ApplicationSettings.SCAN_FOLDER + "\\" + filename;

            Conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + TestFilePath + ";Extended Properties=\"Excel 8.0;HDR=YES;\""); 
            //Conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\selenium.xls;Extended Properties=\"Excel 8.0;HDR=YES;\"");
        }

        /// <summary>
        /// Method to Get All the Records from Excel
        /// </summary>
        /// <returns></returns>
        public async Task<ObservableCollection<ExcelTestScript>> GetDataFormExcelAsync()
        {
            ObservableCollection<ExcelTestScript> ExcelTestDrivers = new ObservableCollection<ExcelTestScript>(); 
            await Conn.OpenAsync();
            Cmd = new OleDbCommand();
            Cmd.Connection = Conn;
            Cmd.CommandText = "Select * from [Sheet1$]";
            var Reader = await Cmd.ExecuteReaderAsync();
            while (Reader.Read())
            {
                try
                {
                    ExcelTestDrivers.Add(new ExcelTestScript()
                    {
                        ID = Int32.Parse(Reader["ID"].ToString()),
                        Run = Reader["Run"].ToString(),
                        Keyword = Reader["Keyword"].ToString(),
                        ObjectDetails = Reader["ObjectDetails"].ToString(),
                        Group = Reader["Group"].ToString(),
                        Action = Reader["Action"].ToString(),
                        Comments = Reader["Comments"].ToString(),
                        IsChange = false
                    });
                }
                catch (Exception e)
                {
                    
                    throw e;
                }
                
            }
            Reader.Close();
            Conn.Close();
            return ExcelTestDrivers;
        }

        /// <summary>
        /// Method to Insert Record in the Excel
        /// S2. If the ExcelTestDriver is already exist, then it is taken for Update
        /// </summary>
        /// <param name="ets"></param>
        public async Task<bool> InsertOrUpdateRowInExcelAsync(ExcelTestScript ets)
        {
            bool IsSave = false;

            await Conn.OpenAsync();
            Cmd = new OleDbCommand();
            Cmd.Connection = Conn;
            Cmd.Parameters.AddWithValue("@Run", ets.Run);
            Cmd.Parameters.AddWithValue("@ID", ets.ID);

            //S2
            if (ets.Keyword != String.Empty)
            {
                Cmd.CommandText = "Update [Sheet1$] set Run=@Run where ID=@ID";
            }

            int result = await Cmd.ExecuteNonQueryAsync();
            if (result > 0)
            {
                IsSave = true;
            }
            Conn.Close();

            return IsSave;

        }

        /// <summary>
        /// The method to check if the record is already available 
        /// in the workgroup
        /// </summary>
        /// <param name="ets"></param>
        /// <returns></returns>
        private async Task<bool> CheckIfRecordExistAsync(ExcelTestScript ets)
        {
            bool IsRecordExist = false;
            Cmd.CommandText = "Select * from [Sheet1$] where ID=@ID";
            var Reader = await Cmd.ExecuteReaderAsync();
            if (Reader.HasRows)
            {
                IsRecordExist = true;
            }
             
            Reader.Close();
            return IsRecordExist;
        }
    }
}
